﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.OleDb;

namespace DAO
{
    public static class LopHocDAO
    {
        public static LopDTO LayDuLieuTuDataRow(DataRow dr)
        {
            LopDTO item = null;
            try
            {
                item = new LopDTO();
                item.ID = dr["ID"].ToString();
                item.IDKhoi = dr["IDKhoi"].ToString();
                item.TenLop = dr["TenLop"].ToString();
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi khi lấy thông tin: " + ex.ToString());
            }
            return item;
        }

        //lấy danh sách đầy đủ
        public static List<LopDTO> LayDanhSach()
        {
            List<LopDTO> lstLopDTO = null;
            try
            {
                lstLopDTO = new List<LopDTO>();
                String query = "SELECT * From Lop";
                DataTable dt = DataProvider.ExecuteQuery(query);
                foreach (DataRow dr in dt.Rows)
                {
                    LopDTO item = new LopDTO();
                    item = LayDuLieuTuDataRow(dr);
                    lstLopDTO.Add(item);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi khi lấy DS: " + ex.ToString());
            }
            return lstLopDTO;
        }


        // get all khoi lop hoc
        public static DataTable GetAllKhoi()
        {
            DataTable result = new DataTable();
            try
            {
                string sql = "select * from Khoi";
                result = DataProvider.ExecuteQuery(sql);
            }
            catch (Exception)
            {

                throw new Exception("Khong the lay khoi lop hoc");
            }
            return result;
        }
        //Get All lop hoc
        public static DataTable GetAllLopHoc()
        {
            DataTable result = new DataTable();
            try
            {
                string sql = "select lop.ID,khoi.ID,lop.TenLop,khoi.TenKhoi from lop,khoi where lop.IDkhoi=khoi.ID";
                result = DataProvider.ExecuteQuery(sql);
                DataColumn stt = new DataColumn("STT", typeof(int));
                result.Columns.Add(stt);
                stt.SetOrdinal(0);
                for (int i = 0; i < result.Rows.Count; i++)
                {
                    result.Rows[i][0] = i + 1;
                }
            }
            catch (Exception)
            {

                throw new Exception("Khong the lay khoi lop hoc");
            }
            return result;
        }

        // gen ID for new row
        public static string GenID()
        {
            string result = "";
            String strID = "";
            //get lastest Lop
            DataTable last = new DataTable();
            string sql = "select max(ID) from lop";
            try
            {
                last = DataProvider.ExecuteQuery(sql);
                if (last.Rows.Count > 0)
                    strID = last.Rows[0][0].ToString();
                int id = int.Parse(strID.Substring(strID.Length - 3, 3)) + 1;
                if (id < 10)
                    result = strID.Substring(0, 1) + "00" + id.ToString();
                else if (id < 100)
                    result = strID.Substring(0, 1) + "0" + id.ToString();
                else
                    result = strID.Substring(0, 1) + id.ToString();
            }
            catch (Exception)
            {

                throw new Exception("Khong lop hoc nao");
            }
            return result;
        }
        // Them Lop
        public static bool AddNewLop(LopDTO lop)
        {
            Boolean kq = false;
            try
            {
                String strQuery = "Insert into Lop values (?,?,?)";
                IList<OleDbParameter> arrParameter = new List<OleDbParameter>();
                string id = GenID();
                arrParameter.Add(new OleDbParameter("@ID", id));
                arrParameter.Add(new OleDbParameter("@TenLop", lop.TenLop));
                arrParameter.Add(new OleDbParameter("@IDKhoi", lop.IDKhoi));
                DataTable dt = DataProvider.ExecuteNonQuery(strQuery, arrParameter);
                kq = true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            return kq;
        }
        //xoa
        public static Boolean Delete(String id)
        {
            Boolean kq = false;
            try
            {
                String strQuery = "Delete From Lop Where id = ?";
                IList<OleDbParameter> arrParameter = new List<OleDbParameter>();
                arrParameter.Add(new OleDbParameter("@ID", id));
                DataTable dt = DataProvider.ExecuteNonQuery(strQuery, arrParameter);
                kq = true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            return kq;
        }
        //cap nhat
        public static Boolean Update(LopDTO lop)
        {
            Boolean kq = false;
            try
            {
                String strQuery = "Update Lop Set tenlop = ?,idkhoi=? Where id=?";
                IList<OleDbParameter> arrParameter = new List<OleDbParameter>();
                arrParameter.Add(new OleDbParameter("@tenlop", lop.TenLop));
                arrParameter.Add(new OleDbParameter("@idkhoi", lop.IDKhoi));
                arrParameter.Add(new OleDbParameter("@id", lop.ID));
                DataTable dt = DataProvider.ExecuteNonQuery(strQuery, arrParameter);
                kq = true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            return kq;
        }

        public static List<LopDTO> LayDanhSachTheoKhoi(string idKhoi)
        {
            List<LopDTO> lstLopDTO = null;
            try
            {
                lstLopDTO = new List<LopDTO>();
                String query = "SELECT * From Lop Where IDKhoi = ?";

                IList<OleDbParameter> arrParameter = new List<OleDbParameter>();
                arrParameter.Add(new OleDbParameter("@IDKhoi", idKhoi));

                DataTable dt = DataProvider.ExecuteNonQuery(query, arrParameter);

                foreach (DataRow dr in dt.Rows)
                {
                    LopDTO item = new LopDTO();
                    item = LayDuLieuTuDataRow(dr);
                    lstLopDTO.Add(item);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi khi lấy DS: " + ex.ToString());
            }
            return lstLopDTO;
        }

    }
}
